<?php

namespace App\Console\Commands;

use App\Models\Building;
use App\Models\Department;
use App\Models\Dorm;
use App\Models\DormMeter;
use App\Models\MeterType;
use App\Models\Staff;
use App\Models\StaffDormAdjust;
use App\Models\StaffPosition;
use Carbon\Carbon;
use Illuminate\Console\Command;
use Illuminate\Support\Arr;
use mysql_xdevapi\Exception;

class ImportCSV extends Command
{
    /**
     * The name and signature of the console command.
     *
     * @var string
     */
    protected $signature = 'import:csv';

    /**
     * The console command description.
     *
     * @var string
     */
    protected $description = 'import staff_dorm.csv';

    /**
     * Create a new command instance.
     *
     * @return void
     */
    public function __construct()
    {
        parent::__construct();
    }

    /**
     * Execute the console command.
     *
     * @return mixed
     */
    public function handle()
    {
        $path = database_path('staff_dorm.csv');
        $path_rec = database_path('record.csv');
        $arr = $this->csvToArray($path);
        $arr_rec = $this->csvToArray($path_rec);

        $q = $this->ask('import staff_dorm.csv?','n');
        if(strtolower($q)=='y'){
            \DB::unprepared('SET FOREIGN_KEY_CHECKS=0');
            $q = $this->ask('truncate building,dorm,department,staff and staffDormAdjust?','n');
            if(strtolower($q)=='y'){
                Building::truncate();
                Dorm::truncate();
                Department::truncate();
                Staff::truncate();
                StaffDormAdjust::truncate();
            }

            foreach ($arr as $i => $item) {
                //索引大于0的条目才处理，可把表头去掉
                if ($i) {
                    //1、创建楼栋
                    $short = strtoupper($item[2]);
                    $name = strtoupper($item[3]);
                    $dormType = $item[1] ? $item[1] : '';
                    if($dormType){
                        $beds = $item[7];
                        $forGender = $item[14] ? ($item[14] == '男' ? 1 : 2) : 0;
                        $building = Building::query()->firstOrCreate(['name'=>$name],[
                            'dorm_type_id' => $dormType,
                            'short_name' => $short,
                            'name' => $name,
                            'floors' => $item[4],
                            'unit_count' => $item[5],
                            'rooms_per_floor' => $item[6],
                            'beds_per_room' => $beds,
                            'gen_dorm_rule' => $item[8]
                        ]);

                        $code = strtoupper($item[0]);
                        $re = str_replace($short, '', $code);
                        $re = str_replace('#', '', $re);
                        $re = str_replace('D', '', $re);
                        $ss = str_split($re);
                        if (count($ss) > 3) {
                            $unit_name = $ss[0];
                            $floor = $ss[1];
                        } else {
                            $unit_name = '1';
                            $floor = $ss[0];
                        }
                        //2、创建宿舍
                        $dorm = Dorm::query()->firstOrCreate(['code'=>$code],[
                                'code' => $code,
                                'dorm_type_id' => $item[1],
                                'building_id' => $building->id,
                                'floor' => $floor,
                                'unit_name' => $unit_name,
                                'beds' => $beds,
                            ]
                        );

                        //3、创建部门
                        if($item[9] && $item[9]!=='#N/A'){
                            $department = Department::query()->firstOrCreate([
                                'name' => $item[9]
                            ]);
                        }

                        //4、创建职位
                        $position = StaffPosition::query()->firstOrCreate([
                            'position' => $item[11]
                        ]);
                        //4、创建人员
                        $staff = Staff::query()->firstOrCreate(['name' => $item[10],'id_card' => $item[12]],[
                            'name' => $item[10],
                            'staff_position_id' => $position->id,
                            'department_id' => optional($department)->id,
                            'dorm_id' => $dorm->id,
                            'sex' => $forGender,
                            'id_card' => $item[12],
                            'phone'=>$item[15],
                            'card_num'=>$item[16],
                        ]);
                        $adjust_date = $item[13]?$item[13]:Carbon::now()->firstOfMonth()->format('Y-m-d');
                        StaffDormAdjust::query()->create(['staff_id'=>$staff->id,
                            'dorm_id_to'=>$dorm->id,'adjust_date'=>$adjust_date],[
                            'staff_id'=>$staff->id,
                            'dorm_id_to'=>$dorm->id,
                            'adjust_date'=>$adjust_date,
                            'adjust_reason'=>'初始化',
                        ]);
                        $this->info("第 $i 条处理成功");
                    }else{
                        $this->warn("第 $i 条未设置宿舍类型，不予处理");
                    }
                }
            }
            $this->info('staff_dorm.csv 数据导入完毕');
        }


        $q = $this->ask('import record.csv?','n');
        if(strtolower($q)=='y'){
            $q = $this->ask('truncate dorm meterts?','n');
            if(strtolower($q)=='y')
            DormMeter::truncate();
                $meterTypes = MeterType::all();
            foreach ($arr_rec as $i=>$rec){
                try {
                    $dorm = Dorm::query()->where('code',$rec[0])->first();
                    if($dorm==null){
                        $like = substr($rec[0],0,strlen($rec[0])-3)."%";
                        $same = Dorm::query()->where('code','like',$like)->first();
                        if($same){
                            $arr = $same->toArray();
                            $arr = Arr::except($arr,'id');
                            $arr['code']=$rec[0];
                            $dorm = Dorm::query()->create($arr);
                        }
                    }
                    if($dorm){
                        foreach ($meterTypes as $k=>$meterType){
                            $nfc_id = $dorm->code.'-'.$meterType->abbr.'1';
                            $created = DormMeter::query()->where('nfc_id',$nfc_id)->exists();
                            if(!$created){
                                $reading = $meterType->abbr=='w'?$rec[1]:$rec[2];
                                $meter=[
                                    'dorm_id'=>$dorm->id,
                                    'meter_type_id'=>$meterType->id,
                                    'nfc_id'=>$nfc_id,
                                    'last_reading'=>$reading?$reading:0,
                                    'last_reading_date'=>Carbon::now()->subMonth(1)->lastOfMonth()->format('Y-m-d'),
                                    'unit'=>$meterType->unit,
                                ];
                                DormMeter::create($meter);
                                $this->info("第 $i 条导入成功");
                            }else{
                                $this->warn("第 $i 条不予处理，$rec[0] 宿舍已经安装了水电表");
                            }
                        }

                    }else{
                        $this->warn("第 $i 条不予处理，$rec[0] 宿舍尚未创建");
                    }
                } catch (Exception $exception) {
                    $msg = $exception->getMessage();
                    $this->info("第 $i 条处理失败，$msg");

                }

            }
            $this->info('record.csv 数据导入完毕');
        }

    }

    public function csvToArray($path)
    {
        $arrCSV = array();
        // Open the CSV
        if (($handle = fopen($path, "r")) !== FALSE) {
            // Set the parent array key to 0
            $key = 0;
            // While there is data available loop through unlimited times (0) using separator (,)
            while (($data = $this->fgetcsv_reg($handle)) !== FALSE) {
                // Count the total keys in each row
                $c = count($data);
                //Populate the array
                for ($x = 0; $x < $c; $x++) {
                    $arrCSV[$key][$x] = $data[$x];
                }
                $key++;
            } // end while
            // Close the CSV file
            fclose($handle);
        } // end if
        return $arrCSV;

    }

    function fgetcsv_reg($handle, $length = null, $d = ',', $e = '"')
    {
        $d = preg_quote($d);
        $e = preg_quote($e);
        $_line = "";
        $eof = false;
        while ($eof != true) {
            $_line .= (empty ($length) ? fgets($handle) : fgets($handle, $length));
            $itemcnt = preg_match_all('/' . $e . '/', $_line, $dummy);
            if ($itemcnt % 2 == 0)
                $eof = true;
        }
        $_csv_line = preg_replace('/(?: |[ ])?$/', $d, trim($_line));
        $_csv_pattern = '/(' . $e . '[^' . $e . ']*(?:' . $e . $e . '[^' . $e . ']*)*' . $e . '|[^' . $d . ']*)' . $d . '/';
        preg_match_all($_csv_pattern, $_csv_line, $_csv_matches);
        $_csv_data = $_csv_matches[1];
        for ($_csv_i = 0; $_csv_i < count($_csv_data); $_csv_i++) {
            $_csv_data[$_csv_i] = preg_replace('/^' . $e . '(.*)' . $e . '$/s', '$1', $_csv_data[$_csv_i]);
            $_csv_data[$_csv_i] = str_replace($e . $e, $e, $_csv_data[$_csv_i]);
        }
        return empty ($_line) ? false : $_csv_data;
    }
}
